# -*- coding: UTF-8 -*-

import pymysql

import common.Logger as Logger
from common.Constants import *

logger = Logger.get_logger(__name__)

def connect_db():
    logger.info('---MySQL_CONNECTING---连接到mysql服务器...---MySQL_CONNECTING---')
    db = pymysql.connect(host = mysql_host, port = mysql_port, user = mysql_user,
                         passwd = mysql_password, db = mysql_db, charset=mysql_encode)
    logger.info("---MySQL_CONNECTED---数据库已连接---MySQL_CONNECTED---")
    return db

def get_cursor(db):
    return db.cursor()

def close_db(db):
    db.close()

def crud(list):

    db = connect_db()
    cursor = get_cursor(db)

    try:
        for province in list:
            # 插入省份信息
            sql = "INSERT INTO tb_district(id, name, url) VALUES (%d, '%s', '%s')" % (province.id, province.name, province.url)
            logger.debug("---MySQL_SQL---准备执行的SQL语句： " + sql)
            try:
                cursor.execute(sql)
                db.commit()
            except Exception as e:
                logging.exception(e)
                logger.error("===数据插入有误，跳过本条数据===")
                continue
            # 判断为港澳台
            # 通过地区列表判断。如果是，特产信息保存在 province.specialty 中
            if len(province.district) == 0:
                for s in province.specialty:
                    sql = "INSERT INTO tb_specialty(id, district_id, province_id, name, url, summary, content, pic) VALUES ('%s', %d, %d, '%s', '%s', '%s', '%s', '%s')" \
                          % (s.id, province.id, province.id, s.name, s.url, s.summary, s.content, s.pic)
                    logger.debug("---MySQL_SQL---准备执行的SQL语句： " + sql)
                    try:
                        cursor.execute(sql)
                        db.commit()
                    except Exception as e:
                        logging.exception(e)
                        logger.error("===数据插入有误，跳过本条数据===")
                        continue
            else:
                # 插入地区信息
                for district in province.district:
                    sql = "INSERT INTO tb_district(id, name, url, parent_id) VALUES (%d, '%s', '%s', %d)" % (district.id, district.name, district.url, district.parent_id)
                    logger.debug("---MySQL_SQL---准备执行的SQL语句： " + sql)
                    try:
                        cursor.execute(sql)
                        db.commit()
                    except Exception as e:
                        logging.exception(e)
                        logger.error("===数据插入有误，跳过本条数据===")
                        continue
                    for s in district.specialty:
                        sql = "INSERT INTO tb_specialty(id, district_id, province_id, name, url, summary, content, pic) VALUES ('%s', %d, %d, '%s', '%s', '%s', '%s', '%s')" \
                              % (s.id, district.id, province.id, s.name, s.url, s.summary, s.content, s.pic)
                        logger.debug("---MySQL_SQL---准备执行的SQL语句： " + sql)
                        try:
                            cursor.execute(sql)
                            db.commit()
                        except Exception as e:
                            logging.exception(e)
                            logger.error("===数据插入有误，跳过本条数据===")
                            continue
        logger.info("---MySQL_SUCCESSED---数据库操作成功！---MySQL_SUCCESSED---")
    except Exception as e:
        db.rollback()
        logger.error("---MySQL_ERROR---数据库操作失败！！！---MySQL_ERROR---")
        logging.exception(e)
    finally:
        close_db(db)

